using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using QLPMT_DTO;

namespace QLPMT_DAO
{
   public class ChiTietBaoCaoDoanhThuDAO
    {
       DataProvider provider;
       public ChiTietBaoCaoDoanhThuDAO()
       {
           provider = new DataProvider();
       }
       public List<ChiTietBaoCaoDoanhThu> GetAll()
       {
           provider.Connect();
           string sql = "select * from ChiTietBaoCaoDoanhThuTheoNgay";
           SqlDataReader reader = provider.ExecuteReader(sql);
           List<ChiTietBaoCaoDoanhThu> list = new List<ChiTietBaoCaoDoanhThu>();
           while (reader.Read())
           {
               string ma = reader.GetString(0);
               string mabaocao = reader.GetString(1);
               DateTime ngay = reader.GetDateTime(2);
               double doanhthu = double.Parse(reader.GetSqlMoney(3).Value.ToString());
               int benhnhan = reader.GetInt32(4);
               int tyle = reader.GetInt32(5);
               ChiTietBaoCaoDoanhThu dto = new ChiTietBaoCaoDoanhThu(ma, mabaocao, ngay, doanhthu, benhnhan, tyle);
               list.Add(dto);
           }
           return list;
       }
       public double DoanhThuThang(int thang)
       {
           provider.Connect();
           string sql = "select sum(thanhtien) from KhamBenh,HoaDon,PhieuKham where KhamBenh.MaKhamBenh=PhieuKham.MaKhamBenh and PhieuKham.MaPhieuKham=HoaDon.MaPhieuKham and DATEPART(mm,NgayKham)=" + thang;
           SqlDataReader reader = provider.ExecuteReader(sql);
           reader.Read();
           double kq = double.Parse(reader.GetSqlMoney(0).Value.ToString());
           provider.Disconnect();
           return kq;
       }

       public List<ChiTietBaoCaoDoanhThu> DoanhThuTheoNgay(int thang)
       {
           double kq = DoanhThuThang(thang);
           List<BaoCaoDoanhThu> listbc=new BaoCaoDoanhThuDAO().GetALL();
           int mabaocao=0;
           if (listbc.Count > 0)
           {
                mabaocao = Convert.ToInt32(listbc[listbc.Count - 1].Mabaocao);
           }
           
           mabaocao+=1;
           string strmabaocao=mabaocao.ToString();
           List<ChiTietBaoCaoDoanhThu> listctbc =new ChiTietBaoCaoDoanhThuDAO().GetAll();
           int ma = 0;
           if (listctbc.Count > 0)
           {
                ma = Convert.ToInt32(listctbc[listctbc.Count - 1].MaChiTiet);
           }
           List<DateTime> list = new KhamBenhDAO().DanhSachNgayKham(thang);
           List<ChiTietBaoCaoDoanhThu> list1 = new List<ChiTietBaoCaoDoanhThu>();
           for (int i = 0; i < list.Count; i++)
           {
               ma=ma+1;
               string strma=ma.ToString();
               provider.Connect();
               string sql = "select Count(MaHoaDon),Sum(ThanhTien) from PhieuKham,KhamBenh,HoaDon where KhamBenh.MaKhamBenh=PhieuKham.MaKhamBenh and PhieuKham.MaPhieuKham=HoaDon.MaPhieuKham and DATEPART(dd,NgayKham)=" + list[i].Day+" and DatePart(mm,NgayKham)="+thang;
               SqlDataReader reader = provider.ExecuteReader(sql);
               reader.Read();
               DateTime ngay = list[i].Date;
               double tien = double.Parse(reader.GetSqlMoney(1).Value.ToString());
               int benhnhan = reader.GetInt32(0);
               double tyle =(tien / kq)*100;
               ChiTietBaoCaoDoanhThu dto = new ChiTietBaoCaoDoanhThu(strma, strmabaocao, ngay, tien, benhnhan, tyle);
               list1.Add(dto);
               provider.Disconnect();
           }
           return list1;   

       }
    }
}
